1. Field of the Invention
This invention relates in general to computer-implemented database systems, and, in particular, to improving insert performance on a multi-system transaction environment.
2. Description of Related Art
Databases are computerized information storage and retrieval systems. A Relational Database Management System (RDBMS) is a database management system (DBMS) which uses relational techniques for storing and retrieving data. Relational databases are organized into tables which consist of rows and columns of data. The rows are formally called tuples or records. A database will typically have many tables and each table will typically have multiple tuples and multiple columns. The tables are typically stored on direct access storage devices (DASD), such as magnetic or optical disk drives for semi-permanent storage.
In a DBMS, data records are stored in table spaces. A table space may contain one or more tables. Each table space contains a number of space map pages. Each space map page covers a number of data pages. One or more records can be stored in a single data page. All data pages within a single table space must have the same page size. Typically, a page contains 4096 bytes.
A space map page identifies the data pages that have enough free space for additional data to be inserted. Each space map page covers a fixed range of data pages. To avoid excessive updates to the space map pages, each space map page maintains only a few attributes for each data page. One of the attributes indicates how much free space remains in a data page.
When inserting a record, one or more space map pages are accessed for choosing a candidate data page. If there is a clustering index defined, the DBMS will attempt to insert the record in the same order as the clustering keys. Maintaining data records in the clustering key order enables more efficient data retrieval when the clustering index is used to retrieve a set of records within a key range.
To prevent updates from getting lost, all space map page updates are serialized. In a single-system environment, a local exclusive ("X") page latch is held before a space map page can be updated. A local X page latch is similar to a lock. As soon as the update is completed, the local X page latch is released.
In a multi-system data sharing environment, the space map page update is serialized for all transactions across all sharing systems. Therefore, the local X page latch is extended to a global scope. The global page latch is referred to as a page P-lock (i.e. physical lock). In a data sharing environment, a space map page can only be updated when both the local page latch and the page P-lock are held in exclusive mode.
Each system in the multi-system data sharing environment maintains P-locks for pages that the system owns. For a given page, since the page P-lock is owned by a system, the system also acquires the local page latch to perform intra-system serialization for updates. The page P-lock is a global lock and can only be held in exclusive mode by one system at a time. Since each system owns the page P-locks for the pages the system owns, for inter-system serialization, an owning system can give up the ownership of the page P-lock when the lock is requested by another system. Then, the updated page is externalized to a high-speed shared electronic store or shared DASD for maintaining coherency of the data before the page P-lock is released.
The process of notifying another system to release the page P-lock is called P-lock negotiation. Since each space map page covers a wide range of data pages, the space map pages are frequently updated in an insert-intensive workload. The overhead of frequent page P-lock negotiations is very high. With high space map page contentions, this could have significant impact on the overall database system throughput. The frequent page P-lock negotiation also has significant impact on transaction response time.
Therefore, there is a need in the art for an improved method of insert performance on a multi-system transaction environment.